
var fs = require('fs');
import sqlite3 = require('sqlite3');
class Sqlite3 {
    db: any
    path: string
    fileName: string
    exist: boolean
    constructor(path: string, fileName: string) {
        const sqlite = sqlite3.verbose();
        this.db = new sqlite.Database(`${path}/${fileName}.sqlite`);
        this.path = path;
        this.fileName = fileName;
        this.exist = fs.existsSync(`${path}/${fileName}.sqlite`);
        if (!this.exist) {
            console.log("Creating db file!");
            fs.openSync(`${path}/${fileName}.sqlite`, 'w');
        };
    }
    printErrorInfo(err: string) {
        console.log(`\x1b[0;33m【sqlite-ERR】:${err}\x1b[0;37m`);
    }
    /**
     * 
     * @param tableName 表名
     * @param params 参数
     * @returns Promise
     */
    CreateTable(tableName: string, params: string): Promise<boolean | Error> {
        //CREATE TABLE Persons(id int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255));
        let sql = `CREATE TABLE if not exists ${tableName} (${params});`
        // console.log(sql.toString());
        return new Promise((resolve, reject) => {
            this.db.serialize(() => {
                this.db.run(sql, (err: Error | null) => {
                    if (null == err) {
                        resolve(true);
                    } else {
                        this.printErrorInfo(err.message);
                        reject(err)
                    }
                });
            });
        })

    };
    /**
 * 插入多条相同格式数据
 * @param {String} tableName 表名
 * @param {Array} keysArray 键数组
 * @param {Array} valuesArray 值数组
 * @example InsertMultipleParamsIntoTable(`t_log`, ["user", "tel", "confTyp", "deviceType", "method", "status", "msg", "para", "time", "t"], [['本地', '13577612570', 'ACTUATOR', 'MAIN_PUMP', 'OPEN', 'success', '打开成功', '{"sta":0}', '2021-09-03 09:56:52', 1630634212000], ['本地', '13577612570', 'ACTUATOR', 'MAIN_PUMP', 'OPEN', 'success', '打开成功', '{"sta":0}', '2021-09-03 09:56:52', 1630634212000]]);
 */
    InsertMultipleParamsIntoTable(tableName: string, keysArray: Array<string>, valuesArray: Array<Array<string | number>>) {
        let keys = keysArray[0];
        let vars = '?';
        for (let i = 1; i < keysArray.length; i++) {
            keys += `,${keysArray[i]}`;
            vars += `,?`;
        }
        let sql = `INSERT INTO ${tableName} (${keys}) values(${vars});`
        this.db.serialize(() => {
            let stmt = this.db.prepare(sql);
            for (let i = 0; i < valuesArray.length; ++i) {
                stmt.run(typeof (valuesArray[i]) == 'string' ? `'${valuesArray[i]}'` : valuesArray[i]);
            }
            stmt.finalize();
        });
    };
    /**
 * 插入一条记录
 * @param {String} tableName 
 * @param {String} keys 
 * @param {String} values 
 * @returns 
 */
    InsertParamsIntoTable(tableName: string, keys: string, values: string): Promise<boolean | Error> {
        let sql = `INSERT INTO ${tableName} (${keys}) values(${values});`
        console.log(sql.toString());
        return new Promise((resolve, reject) => {
            this.db.serialize(() => {
                this.db.run(sql, (err: Error | null, result: any) => {
                    if (null == err) {
                        resolve(result);
                    } else {
                        this.printErrorInfo(err.message);
                        reject(err)
                    }
                });
            });
        })
    };

    /**查询满足条件的数据
     * 
     * @param {*} tableName 表名
     * @param {*} condition 条件
     */
    QueryConditionalDataFromTable(tableName: string, condition: string | null | "" | undefined): Promise<any | Error> {
        let sql = `SELECT * FROM ${tableName}`;
        sql = (condition == undefined || condition == null || condition == '') ? sql + ';' : sql + ` WHERE ${condition};`
        // console.log(sql.toString());
        return new Promise((resolve, reject) => {
            this.db.all(sql, (err: Error | null, result: any) => {
                if (null == err) {
                    // console.log(err, result)
                    resolve(result);
                } else {
                    this.printErrorInfo(err.message);
                    reject(err)
                }
            });
        })
    }
    /**查询满足条件的数据
     * 
     * @param {*} tableName 表名
     * @param {*} condition 条件
     */
    DeleteConditionalDataFromTable(tableName: string, condition: string | null | "" | undefined): Promise<any | Error> {
        let sql = `DELETE FROM ${tableName}`;
        // console.log(sql.toString());
        sql = (condition == undefined || condition == null || condition == '') ? sql + ';' : sql + ` WHERE ${condition};`
        return new Promise((resolve, reject) => {
            this.db.all(sql, (err: Error | null, result: any) => {
                if (null == err) {
                    console.log(err, result)
                    resolve(result);
                } else {
                    this.printErrorInfo(err.message);
                    reject(err)
                }
            });
        })
    }

    /**
     * 查询某个表所有数据
     * @param {*} tableName 
     * @returns 
     */
    QueryAllDataFromTable(tableName: string): Promise<any | Error> {
        let sql = `SELECT * FROM ${tableName};`;
        // console.log(sql.toString());
        return new Promise((resolve, reject) => {
            this.db.all(sql, (err: Error | null, result: any) => {
                if (null == err) {
                    // console.log(err, result)
                    resolve(result);
                } else {
                    this.printErrorInfo(err.message);
                    reject(err)
                }
            });
        })
    }
    /**
 * 查询表是否存在
 * @param {*} tableName 
 * @returns 
 */
    TableIsExists(tableName: string): Promise<boolean | Error> {
        let sql = `SELECT count(*) FROM sqlite_master WHERE type="table" AND name ='${tableName}';`
        // console.log(sql.toString());
        return new Promise((resolve, reject) => {
            this.db.all(sql, (err: Error | null, result: any) => {
                if (null == err) {
                    // console.log(result)
                    if (result[0]['count(*)'] > 0) {
                        resolve(true);
                    } else {
                        reject(`表${tableName}不存在！！`)
                    }
                } else {
                    this.printErrorInfo(err.message);
                    reject(err)
                }
            });
        })
    }
    /**
     * 更新某个表的某些字段
     * @param {string} tableName 表名称
     * @param {Array} keysArray 键数组
     * @param {Array} valuesArray 值数组
     * @param {string} condition 条件 null/undefine/'' 不需要附加条件
     * @example UpdateMultipleParamsIntoTable(`t_log`,["user","tel"],["admin","13577XXXXXX"],`deviceType='MIX_PUMP'`).then(()=>{
     *      console.log(`更新成功`)
     * }).catch((err)=>{
     *      console.log(err)
     * })
     */
    UpdateMultipleParamsIntoTable(tableName: string, keysArray: Array<string>, valuesArray: Array<string | number>, condition: string | null | undefined): Promise<any | Error> {
        let value0 = typeof (valuesArray[0]) == "string" ? `'${valuesArray[0]}'` : valuesArray[0];
        let updateStr = `${keysArray[0]}=${value0}`;
        for (let index = 1; index < keysArray.length; index++) {
            let value = typeof (valuesArray[index]) == "string" ? `'${valuesArray[index]}'` : valuesArray[index];
            updateStr += `,${keysArray[index]}=${value}`;
        }
        let sql = `UPDATE ${tableName} SET ${updateStr}`;
        sql = (condition == undefined || condition == null || condition == '') ? sql + ';' : sql + ` WHERE ${condition};`
        // console.log(sql.toString());
        return new Promise((resolve, reject) => {
            this.db.serialize(() => {
                this.db.run(sql, (err: Error | null, result: any) => {
                    if (null == err) {
                        resolve(result);
                    } else {
                        this.printErrorInfo(err.message);
                        reject(err)
                    }
                });
            });
        });
    }
    SelectCountFromTable(tableName: string, condition: string): Promise<number | Error> {
        //select count(f_masterSn) from t_roller_slaver;
        let sql = `SELECT count(*) FROM ${tableName}`
        sql = (condition == undefined || condition == null || condition == '') ? sql + ';' : sql + ` WHERE ${condition};`
        // console.log(sql.toString());
        return new Promise((resolve, reject) => {
            this.db.all(sql, (err: Error | null, result: any) => {
                if (null == err) {
                    resolve(result[0]['count(*)']);
                } else {
                    this.printErrorInfo(err.message);
                    reject(err)
                }
            });
        })
    }
    /**
     * 执行SQL指令
     * @param {*} sql 
     */
    ExecuteSql(sql: string) {
        this.db.run(sql, function (err: Error | null) {
            if (null != err) {
                this.printErrorInfo(err.message);
            }
        });
    };
    /**
     * 断开连接
     */
    close() {
        this.db.close();
    };

    /**获取传感器历史数据条目数
     * 
     * @param {*} tableName 
     * @param {*} updt 
     * @param {*} downdt 
     */
    GetHistoryDataLenth(tableName: string, updt: string | number | Date, downdt: string | number | Date, condition: string): Promise<number | Error> {
        // SELECT * FROM t_irr_fer_log WHERE (t BETWEEN 1630541896000 AND 1630570696000) AND deviceType='MIX_PUMP';
        let selectsql = `SELECT * FROM ${tableName} WHERE (t BETWEEN ${Date.parse(new Date(downdt).toString())} AND ${Date.parse(new Date(updt).toString())}) AND ${condition}`;
        // let selectsql = `SELECT * FROM ${tableName}`;
        if (condition == undefined || condition == "" || condition == null) {
            selectsql = `SELECT * FROM ${tableName} WHERE t BETWEEN ${Date.parse(new Date(downdt).toString())} AND ${Date.parse(new Date(updt).toString())}`;
            // selectsql = `SELECT * FROM ${tableName}`;
        }
        console.log(`${new Date().toLocaleString()}GetHistoryDataLenthquerysqlis===>`, selectsql.toString());
        return new Promise((resolve, reject) => {
            this.db.all(selectsql, (err: Error | null, result: any) => {
                if (err) {
                    // console.log(`${new Date().toLocaleString()}`,'[SELECT err] - ', err.message);
                    reject(err);
                } else {
                    // console.log(result);
                    resolve(result.length);
                }
            });
        })
    }
    /**
     * 
     * @param {string} tableName 表名
     * @param {number} updt 时间上限
     * @param {number} downdt 时间下限
     * @param {number} offset 偏移量
     * @param {number} limit 查询数量
     */
    SelectHistoryDatas(tableName: string, updt: string | number | Date, downdt: string | number | Date, offset: number, limit: number, condition: string | null | undefined): Promise<any | Error> {
        //SELECT * FROM t_irr_fer_log WHERE (t BETWEEN 1630541896000 AND 1630570696000) AND deviceType='MIX_PUMP' LIMIT 0,5;
        let selectsql = `SELECT * FROM ${tableName} WHERE (t BETWEEN ${Date.parse(new Date(downdt).toString())} AND ${Date.parse(new Date(updt).toString())}) AND ${condition} order by t desc LIMIT ${offset},${limit}`;
        // let selectsql = `SELECT * FROM ${tableName}`;
        if (condition == undefined || condition == "" || condition == null) {
            selectsql = `SELECT * FROM ${tableName} WHERE t BETWEEN ${Date.parse(new Date(downdt).toString())} AND ${Date.parse(new Date(updt).toString())} order by t desc LIMIT ${offset},${limit} `;
            // selectsql = `SELECT * FROM ${tableName}`;
        }
        console.log(`${new Date().toLocaleString()}`, selectsql.toString());
        return new Promise((resolve, reject) => {
            this.db.all(selectsql, (err: Error | null, result: any) => {
                if (err) {
                    // console.log(`${new Date().toLocaleString()}`,'[SELECT err] - ', err.message);
                    reject(err);
                } else {
                    resolve(result);
                }
            });
        })
    }
}

export = Sqlite3;
